package com.common.project.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SqlUtils {


    public  static List<Map> getListBySql(String sql){

//         String DB_URL = "jdbc:mysql://10.16.8.78:3306/project?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true";
         String DB_URL = "jdbc:mysql://39.106.104.195:3309/project?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useAffectedRows=true";
         String USER = "root";
         String PASS = "pass1234";
//         String PASS = "2vNd5#ipBhaiygJQ";
        Connection conn = null;
        Statement stmt = null;
        List<Map> list=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();

           /* String sql = "SELECT u.gong_zi_bian_hao,u.no,u.name,j.chu_sheng_ri_qi,j.zheng_zhi_mian_mao" +
                    " from USER u left join JIA_TING_XIN_XI j " +
                    " on u.user_id=j.user_id " +
                    " where u.user_id='1062242704961961984'";*/

           /* String sql = "SELECT u.gong_zi_bian_hao,u.no,u.name,j.chu_sheng_ri_qi,d.type_value ziJiZhengZhiMianMao,d1.type_value jiaTingChengYuanZhengZhiMianMao" +
                    " from USER u left join JIA_TING_XIN_XI j " +
                    " on u.user_id=j.user_id " +
                    " left join dictionaries d" +
                    " on u.zheng_zhi_mian_mao=d.id" +
                    " left join dictionaries d1 " +
                    " on j.zheng_zhi_mian_mao =d1.id"+
                    " where u.user_id='1062242704961961984'";*/
            ResultSet rs = stmt.executeQuery(sql);


            list = convertList(rs);
            rs.close();
        }catch(SQLException se){
            se.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(stmt!=null)
                    conn.close();
            }catch(SQLException se){
            }
            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        return list;

    }

    private static List<Map> convertList(ResultSet rs) throws SQLException{
        List<Map> list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取行的数量
        while (rs.next()) {
            Map rowData = new HashMap();//声明Map
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnLabel(i), rs.getObject(i)==null?"":rs.getObject(i));//获取键名及值
            }
            list.add(rowData);
        }
        return list;
    }
}
